"""
Custom managers for most of the models; these add useful logic for
various custom filters and queries.

"""

from django.db import models

from django.contrib.auth.models import User
from django.db import connection
class TipsManager(models.Manager):
    """
    Custom manager for the Snippet model.
    
    Adds shortcuts for common filtering operations, and for retrieving
    popular related objects.
    
    """
    def get_by_author(self, username):
        """
        Returns a QuerySet of tips.
        
        """
        return self.filter(author__username__exact=username)
    
    
    def get_by_tag(self, tag_slug):
        """
        Returns a QuerySet of Snippets which have a particular Tag.
        
        """
        return self.filter(tags__slug__exact=tag_slug)
    
    def top_items(self, item_type, num=5):
        """
        Returns a list of the top ``num`` objects of a particular
        type, based on the number of Snippets associated with them;
        for example, with ``item_type=tag``, returns a list of Tags
        based on how many Snippets they've been used for.
        
        Acceptable values for ``item_type`` are:
        
         * 'author' -- will return the users who have submitted the
           most Snippets.
         * 'language' -- will return the most-used languages.
         * 'tag' -- will return the most-used tags.
        
        """
        # Need all of these up-front so the mapping dictionary can be built
        # correctly.
        
        from dolist.apps.tip.models import *
        
        # ``_meta`` strikes again.
        object_mapping = {
            'author': {'model': User,
                       'primary_table': User._meta.db_table,
                       'secondary_table': Tip._meta.db_table },
            'tag': { 'model': Tag,
                     'primary_table': Tag._meta.db_table,
                     'secondary_table': Tip._meta.get_field('tags').m2m_db_table() },
            }
        
        params = object_mapping[item_type]
        query = """SELECT p.id AS object_id, COUNT(*) AS score
        FROM %s p INNER JOIN %s s
        ON p.id = s.%s
        GROUP BY object_id
        ORDER BY score DESC""" % (params['primary_table'], params['secondary_table'], item_type + '_id')
        
        
        cursor = connection.cursor()
        cursor.execute(query, [])
        object_ids = [row[0] for row in cursor.fetchall()[:num]]
        
        # Use ``in_bulk`` here instead of an ``id__in`` lookup, because ``id__in``
        # would clobber the ordering.
        object_dict = params['model']._default_manager.in_bulk(object_ids)
        return [object_dict[object_id] for object_id in object_ids]
    
class TagsManager(models.Manager):
    """
    Custom manager for the tag model.

    Adds shortcuts for common filtering operations, and for retrieving
    popular related objects.

    """
    def get_by_author(self, user_id):
        """
        Returns a QuerySet of Tags which user.

        """


        # ``_meta`` strikes again.
        from dolist.apps.tip.models import *

        params = {   
                     'primary_table': Tag._meta.db_table,
                     'secondary_table': Tip._meta.get_field('tags').m2m_db_table(),
                     'third_table':Tip._meta.db_table }
                     
        query = """SELECT p.id AS object_id, COUNT(*) AS score
        FROM %s p INNER JOIN %s s ON p.id = s.%s 
        INNER JOIN %s v on s.tip_id=v.id and  v.author_id=%s
        GROUP BY object_id
        ORDER BY score DESC""" % (params['primary_table'], params['secondary_table'],'tag_id',params['third_table'],user_id)

        cursor = connection.cursor()
        cursor.execute(query, [])
        object_ids = [row[0] for row in cursor.fetchall()]

        # Use ``in_bulk`` here instead of an ``id__in`` lookup, because ``id__in``
        # would clobber the ordering.
        object_dict = Tag._default_manager.in_bulk(object_ids)
        return [object_dict[object_id] for object_id in object_ids]